﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Database;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{
   public class GreigeMasterReport : BaseReport
    {
        #region Variable
        byte[] b;
        string MultiRowTable = "WASTGA;WASTPASS;BASTGA;GASTGA;WTLEIST;BASTKE;";
        int WarpBeamEnds11 = 0;
        int TotalEnds = 0;
        int TotalSelvage = 0;
        string ArticleNum;
        string ArticleVar;
        string ArticleColor;
        string ArticleFin;
        string strCompany;
        string strExcelFile;
        double Warpdensity = 1.0;
        double Weftdensity = 1.0;

        double WarpEnds = 1.0;
        double WeftEnds = 1.0;
        double Jdense = 0.0;
        double Wdense = 0.0;
        double TotalDense = 0.0;
        #endregion
        public GreigeMasterReport()
            : base()
        {
        }
        public GreigeMasterReport(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            ArticleNum = dicParamater["ArticleNum"].ToString().Trim();
            ArticleVar = dicParamater["ArticleVar"].ToString().Trim();
            ArticleColor = dicParamater["ArticleColor"].ToString().Trim();
            ArticleFin = dicParamater["ArticleFin"].ToString().Trim();
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
        }
        private void FillInWarpBeam(string TkeyType, DataRow dwWarpBeam)
        {

            Queue CellQueue = new Queue();

            string beamposition = "";
            string PatternPre = "W_P";
            string RepeatePre = "W_R";
            string PatternField = "WeavingBeamPatternString";
            string BeamArticle = "";
            string BeamVar = "";
            string BeamColor = "";
            string BeamComments = "";
            string Cellname = "";
            string Value = "";
            #region WeavingBeamKey
            #region WS31
            if (TkeyType == "WS31")
            {
                beamposition = dwWarpBeam["WAKPOS"].ToString();
                beamposition = Right("00" + (10 + Convert.ToInt16(beamposition)).ToString(), 2);
                PatternPre = "W_P" + beamposition;
                RepeatePre = "W_R" + beamposition;
                BeamArticle = dwWarpBeam["WAKKART"].ToString();
                BeamVar = dwWarpBeam["WAKKVAR"].ToString();
                BeamColor = GetGB2312Value(dwWarpBeam["WAKKFARBE"].ToString());
                CellQueue.Enqueue("WarpBeamArticle" + beamposition);
                CellQueue.Enqueue("WAKKART");
                CellQueue.Enqueue("WarpBeamVar" + beamposition);
                CellQueue.Enqueue("WAKKVAR");
                CellQueue.Enqueue("WarpBeamColor" + beamposition);
                CellQueue.Enqueue("WAKKFARBE");
                CellQueue.Enqueue("WeavingBeamFactor" + beamposition);
                CellQueue.Enqueue("WAKEAFAKTR");
                CellQueue.Enqueue("WarpBeamPosition" + beamposition);
                CellQueue.Enqueue("WAKPOS");
            }
            #endregion

            #region BS09
            if (TkeyType == "BS09")
            {
                beamposition = dwWarpBeam["BAKPOS"].ToString();
                beamposition = Right("00" + Convert.ToInt16(beamposition).ToString().Trim(), 2);
                PatternPre = "W_P" + beamposition;
                RepeatePre = "W_R" + beamposition;
                BeamArticle = dwWarpBeam["BAKKART"].ToString();
                BeamVar = dwWarpBeam["BAKKVAR"].ToString();
                BeamColor = dwWarpBeam["BAKKFARBE"].ToString();
                CellQueue.Enqueue("WarpBeamPattern" + beamposition);
                CellQueue.Enqueue("BAKPATT");
                CellQueue.Enqueue("WarpBeamEnds" + beamposition);
                CellQueue.Enqueue("BAKKART");
                CellQueue.Enqueue("WarpBeamWarpPattern" + beamposition);
                CellQueue.Enqueue("BAKKART");
                CellQueue.Enqueue("WarpBeamArticle" + beamposition);
                CellQueue.Enqueue("BAKKART");
                CellQueue.Enqueue("WarpBeamVar" + beamposition);
                CellQueue.Enqueue("BAKKVAR");
                CellQueue.Enqueue("WarpBeamColor" + beamposition);
                CellQueue.Enqueue("BAKKFARBE");
                CellQueue.Enqueue("WarpBeamFactor" + beamposition);
                CellQueue.Enqueue("BAKANZ");
                CellQueue.Enqueue("WarpBeamPosition" + beamposition);
                CellQueue.Enqueue("BAKPOS");
            }
            #endregion
            FillInCellbyQueue(CellQueue, dwWarpBeam);
            #endregion

            #region WeavingBeamColorInfor
            var query = from dwBSFARB in dsSource.Tables["BSFARB"].AsEnumerable()
                        where dwBSFARB.Field<string>("BSFKART") == BeamArticle
                        && dwBSFARB.Field<string>("BSFKVAR") == BeamVar
                        && dwBSFARB.Field<string>("BSFKFARBE") == BeamColor
                        select dwBSFARB;
            CellQueue.Clear();
            foreach (DataRow dwQuery in query)
            {
                //WarpBeamPattern01	01	01	01	01	01	01	WarpBeamPatternNameWarpPattern	01	01																																												
                CellQueue.Enqueue("BeamColorDes" + beamposition);
                CellQueue.Enqueue("BSFBEZ");
                FillInCellbyQueue(CellQueue, dwQuery);
                Cellname = "BeamComments" + beamposition;
                Value = dwQuery["COMMENTS"].ToString().Trim().Replace("\n", "").Replace(" ", "");
                /*FillInCellbyQueue(CellQueue, dwQuery);*/
                exAgent.SetCellValue(Cellname, Value, true);
            }
            #endregion

            #region WeavingBeamBase
            var queryWeavingBeamBase = from dwBSSATZ in dsSource.Tables["BSSATZ"].AsEnumerable()
                                       where dwBSSATZ.Field<string>("BSKART") == BeamArticle
                                       select dwBSSATZ;
            CellQueue.Clear();
            string WarpBeamPatternName = "";

            foreach (DataRow dwQuery in queryWeavingBeamBase)
            {
                if (TkeyType == "WS31")
                {
                    WarpEnds = System.Convert.ToDouble(dwQuery["BSFDZ"].ToString());
                }
                CellQueue.Enqueue("WarpBeamEnds" + beamposition);
                CellQueue.Enqueue("BSFDZ");
                CellQueue.Enqueue("WarpBeamPatternName" + beamposition);
                CellQueue.Enqueue("BSSRFNR");
                CellQueue.Enqueue("WarpBeamBase" + beamposition);
                CellQueue.Enqueue("BSFAKTOR");
                WarpBeamEnds11 = System.Convert.ToInt16(dwQuery["BSFDZ"].ToString());
                FillInCellbyQueue(CellQueue, dwQuery);
                WarpBeamPatternName = dwQuery["BSSRFNR"].ToString();
            }
            #endregion

            #region WeavingPattern
            PatternField = "WarpBeamWarpPattern" + beamposition;
            FillInWarpPattern(PatternField, PatternPre, RepeatePre, WarpBeamPatternName, beamposition);
            #endregion

            #region WarpBeam
            var queryWarpBeam = from dwBASTKE in dsSource.Tables["BASTKE"].AsEnumerable()
                                where dwBASTKE.Field<string>("BAKART") == BeamArticle
                                && dwBASTKE.Field<string>("BAKVAR") == BeamVar
                                && dwBASTKE.Field<string>("BAKFARBE") == BeamColor
                                select dwBASTKE;
            foreach (DataRow dwQuery in queryWarpBeam)
            {
                FillInWarpBeam("BS09", dwQuery);
            }

            #endregion
        }
        private void FillInDrafting(DataRow dwWASTPASS)
        {

            Queue CellQueue = new Queue();
            string draftposition = "";
            string DraftNumber = "";
            draftposition = dwWASTPASS["WSPPOS"].ToString();
            draftposition = Right("00" + Convert.ToInt16(draftposition).ToString().Trim(), 2);

            DraftNumber = dwWASTPASS["WSPPASS"].ToString();
            CellQueue.Enqueue("WSPPASS" + draftposition);
            CellQueue.Enqueue("WSPPASS");
            FillInCellbyQueue(CellQueue, dwWASTPASS);
            var query = from dwWTPASS in dsSource.Tables["WTPASS"].AsEnumerable()
                        where dwWTPASS.Field<string>("WTPPASSNR") == DraftNumber
                        select dwWTPASS;
            CellQueue.Clear();
            foreach (DataRow dwQuery in query)
            {
                CellQueue.Enqueue("WTPBEZ" + draftposition);
                CellQueue.Enqueue("WTPBEZ");
                FillInCellbyQueue(CellQueue, dwQuery);
            }
        }
        private void FillInSelvage(DataRow dwWASTKE)
        {

            Queue CellQueue = new Queue();
            string LRDes = "";
            string LDes = "";
            string RDes = "";
            string draftposition = "";
            string LeftSelvage = "";
            string RightSelvage = "";
            string SelvageSide = "L";
            LeftSelvage = dwWASTKE["WAKLLEIST"].ToString().Trim();
            RightSelvage = dwWASTKE["WAKRLEIST"].ToString().Trim();

            var query = (from dwWTLEIST in dsSource.Tables["WTLEIST"].AsEnumerable()
                         where dwWTLEIST.Field<string>("WTLLEIST").Trim() == LeftSelvage
                         select dwWTLEIST
                        ).Distinct();
            CellQueue.Clear();
            int Times = 0;
            foreach (DataRow dwQuery in query)
            {
                if (Times < 1)
                {
                    Times++;
                    CellQueue.Enqueue(SelvageSide + "EdgeName");
                    CellQueue.Enqueue("WTLLEIST");
                    CellQueue.Enqueue(SelvageSide + "EdgeDes");
                    CellQueue.Enqueue("WTLBEZ");
                    LDes = dwQuery["WTLBEZ"].ToString().Trim();
                    CellQueue.Enqueue(SelvageSide + "EdgeDraft");
                    CellQueue.Enqueue("WTLPASSNR");
                    CellQueue.Enqueue(SelvageSide + "EdgeWeave");
                    CellQueue.Enqueue("WTLBDG");
                    CellQueue.Enqueue(SelvageSide + "EdgeEnds");
                    CellQueue.Enqueue("WTLFDZ");
                    CellQueue.Enqueue(SelvageSide + "EdgeDent");
                    CellQueue.Enqueue("WTLEINZNR");
                    TotalSelvage += System.Convert.ToInt16(dwQuery["WTLFDZ"].ToString());

                    FillInCellbyQueue(CellQueue, dwQuery);
                }
            }
            SelvageSide = "R";
            query = (from dwWTLEIST in dsSource.Tables["WTLEIST"].AsEnumerable()
                     where dwWTLEIST.Field<string>("WTLLEIST").Trim() == RightSelvage
                     select dwWTLEIST).Distinct();
            CellQueue.Clear();
            Times = 0;
            foreach (DataRow dwQuery in query)
            {
                if (Times < 1)
                {
                    Times++;
                    CellQueue.Enqueue(SelvageSide + "EdgeName");
                    CellQueue.Enqueue("WTLLEIST");
                    CellQueue.Enqueue(SelvageSide + "EdgeDes");
                    CellQueue.Enqueue("WTLBEZ");
                    RDes = dwQuery["WTLBEZ"].ToString().Trim();
                    CellQueue.Enqueue(SelvageSide + "EdgeDraft");
                    CellQueue.Enqueue("WTLPASSNR");
                    CellQueue.Enqueue(SelvageSide + "EdgeWeave");
                    CellQueue.Enqueue("WTLBDG");
                    CellQueue.Enqueue(SelvageSide + "EdgeEnds");
                    CellQueue.Enqueue("WTLFDZ");
                    CellQueue.Enqueue(SelvageSide + "EdgeDent");
                    CellQueue.Enqueue("WTLEINZNR");
                    FillInCellbyQueue(CellQueue, dwQuery);
                    TotalSelvage += System.Convert.ToInt16(dwQuery["WTLFDZ"].ToString());
                }
            }
            LRDes = LDes + "+" + RDes;
            exAgent.SetCellValue("LRDes", LRDes, true);
            exAgent.SetCellValue("TotalSelvage", TotalSelvage, true);
        }
        private void FillInWeave(DataRow dwWASTAMM)
        {

            Queue CellQueue = new Queue();

            string Weave = "";
            string CellName = "Drafting";
            string ColumnName = "WATEXTE1";
            string CellValue = dwWASTAMM[ColumnName].ToString().Replace("  ", ""); ;
            exAgent.SetCellValue(CellName, CellValue, true);
            CellName = "WAFTCOMS";
            ColumnName = "WATEXTE2";
            CellValue = dwWASTAMM[ColumnName].ToString().Replace("  ", ""); ;
            exAgent.SetCellValue(CellName, CellValue, true);
            CellName = "LEdgeDraftCOMS";
            ColumnName = "WATEXTE3";
            CellValue = dwWASTAMM[ColumnName].ToString().Replace("  ", ""); ;
            exAgent.SetCellValue(CellName, CellValue, true);
            Weave = dwWASTAMM["WABIND"].ToString().Trim();
            CellQueue.Enqueue("WABIND");
            CellQueue.Enqueue("WABIND");
            FillInCellbyQueue(CellQueue, dwWASTAMM);
            string Client = "00";
            string Lang = "V";
            Weave = Weave + "                                       ";
            Weave = Client + Weave.Substring(0, 21) + Lang;
            var query = from dwWeave in dsSource.Tables["SDDAT"].AsEnumerable()
                        where dwWeave.Field<string>("SDSA") == "0109"
                        && dwWeave.Field<string>("SDKEY") == Weave
                        select dwWeave;
            CellQueue.Clear();
            foreach (DataRow dwQuery in query)
            {
                CellName = "WeaveDes";
                ColumnName = "SDDATEN";
                CellValue = GetGB2312Value(dwQuery[ColumnName].ToString().Substring(1, 40).Trim());
                exAgent.SetCellValue(CellName, CellValue, true);
                CellName = "WeaveCom";
                CellValue = dwQuery["COMMENTCN"].ToString().Replace("     ", "");
                exAgent.SetCellValue(CellName, CellValue, true);
            }
        }
        private void FillInFinish(DataTable dtFinish)
        {

            Queue CellQueue = new Queue();
            CellQueue.Clear();
            foreach (DataRow dwFinish in dtFinish.Rows)
            {
                CellQueue.Enqueue("FinishingDes");
                CellQueue.Enqueue("WAFBEZ");
                FillInCellbyQueue(CellQueue, dwFinish);
            }
        }
        private void FillInComposition(DataTable dtWASTAMM)
        {

            Queue CellQueue = new Queue();
            CellQueue.Clear();
            foreach (DataRow dwWASTAMM in dtWASTAMM.Rows)
            {
                CellQueue.Enqueue("COMPOSITION");
                CellQueue.Enqueue("ComCN");
                FillInCellbyQueue(CellQueue, dwWASTAMM);
            }
        }
        private void FillInWeaveColor(DataTable dtWAFARBEN)
        {
            try
            {

                Queue CellQueue = new Queue();
                foreach (DataRow dwWAFARBEN in dtWAFARBEN.Rows)
                {
                    CellQueue.Enqueue("WAFZUSATZ");
                    CellQueue.Enqueue("WAFZUSATZ");
                    CellQueue.Enqueue("WAFCOLORIT");
                    CellQueue.Enqueue("WAFZUSATZ");
                    CellQueue.Enqueue("COMS");
                    CellQueue.Enqueue("COMS");
                    FillInCellbyQueue(CellQueue, dwWAFARBEN);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInWeftPattern(DataTable dtWTSCHUSS)
        {
            Dictionary<string, string> WeftMatrix = PatternMatrix(22, 40, true);
            Array WR = Array.CreateInstance(typeof(Int32), 4, 698);
            Array WM = Array.CreateInstance(typeof(Int32), 22, 698);
            string PatternS = "";
            string YarnEnds = "";
            Queue WRWRQueue = new Queue();
            Queue CellQueue = new Queue();
            CellQueue.Clear();
            foreach (DataRow dwWTSCHUSS in dtWTSCHUSS.Rows)
            {
                CellQueue.Enqueue("WeftPatternString");
                CellQueue.Enqueue("WTSBEZ");
                CellQueue.Enqueue("WPatternEnds");
                CellQueue.Enqueue("WTSSRAPP");
                WeftEnds = System.Convert.ToDouble(dwWTSCHUSS["WTSSRAPP"].ToString());
                FillInCellbyQueue(CellQueue, dwWTSCHUSS);

                WRWRQueue.Enqueue(WR);
                WRWRQueue.Enqueue(WM);
                WRWRQueue = HandleWTSSCHUSS(WRWRQueue, dwWTSCHUSS, "F_P", "F_R");
                WR = (Array)WRWRQueue.Dequeue();
                WM = (Array)WRWRQueue.Dequeue();
            }
        }
        private string Byte2String(byte[] arrByte)
        {

            StringBuilder sb = new StringBuilder();

            foreach (byte b in arrByte)
            {

                sb.Append(b > 15 ? Convert.ToString(b, 16) : '0' + Convert.ToString(b, 16));

            }

            return sb.ToString();

        }
        public Queue HandleWTSSCHUSS(Queue WRWRQueue, DataRow dwWTSCHUSS, string PatternPre, string RepeatePre)
        {
            Array WR, WM;
            string CellName = "";
            string CellValue = "";
            string WTBTAB;
            string FieldName = "WTSSCHUSS01";
            b = (byte[])(dwWTSCHUSS[FieldName]);
            WTBTAB = Byte2String(b);
            FieldName = "WTSSCHUSS02";
            b = (byte[])(dwWTSCHUSS[FieldName]);
            WTBTAB += Byte2String(b);

            //WTBTAB = "0002001E0000000000000000000300270000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000";
            int ColStart = 0;
            WR = (Array)WRWRQueue.Dequeue();
            WM = (Array)WRWRQueue.Dequeue();
            string ColI = "";
            string WBi, WBj;
            int YarnI, EndsI, R1i, R2i, R3i, R4i;
            try
            {
                for (int i = 1; i <= 100; i++)
                {
                    //    "0002001E0000000000000000"
                    ColI = WTBTAB.Substring((i - 1) * 16, 16).ToUpper();
                    YarnI = System.Convert.ToInt16(ColI.Substring(0, 4), 16) - 1;
                    EndsI = Convert.ToInt32(ColI.Substring(4, 4), 16);
                    R1i = System.Convert.ToInt16(ColI.Substring(8, 4), 16);
                    WR.SetValue(R1i, 0, ColStart + i - 1);
                    R2i = System.Convert.ToInt16(ColI.Substring(12, 4), 16);
                    WR.SetValue(R2i, 1, ColStart + i - 1);
                    //EndsI = System.Convert.ToInt16(ColI.Substring(4, 4));

                    if (YarnI > -1)
                    {
                        WM.SetValue(EndsI, YarnI, ColStart + i - 1);
                    }
                }
                WRWRQueue.Enqueue(WR);
                WRWRQueue.Enqueue(WM);
                string PatternS = BuildPatternString(WR, WM, "WTSCHUSS");
                PatternS = PatternS.Replace("+", "");
                string PatternField = "WeftPatternString";
                CellName = PatternField;
                exAgent.SetCellValue(CellName, PatternS, true);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return WRWRQueue;
        }
        private Queue HandleWTBTAB(Queue WRWRQueue, string FieldName, DataRow dwWTSAER, string PatternPre, string RepeatePre)
        {
            Array WR, WM;
            string CellName = "";
            string CellValue = "";
            b = (byte[])(dwWTSAER[FieldName]);
            string WTBTAB = Byte2String(b);

            //WTBTAB = "0002001E0000000000000000000300270000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000";
            int ColStart = System.Convert.ToInt16(FieldName.Substring(6, 3)) - 1;
            int ColEnd = System.Convert.ToInt16(FieldName.Substring(9, 3)) - 1;
            WR = (Array)WRWRQueue.Dequeue();
            WM = (Array)WRWRQueue.Dequeue();
            string ColI = "";
            string WBi, WBj;
            int YarnI, EndsI, R1i, R2i, R3i, R4i;
            try
            {
                for (int i = 1; i <= 10; i++)
                {
                    //    "0002001E0000000000000000"
                    ColI = WTBTAB.Substring((i - 1) * 24, 24).ToUpper();
                    YarnI = System.Convert.ToInt16(ColI.Substring(0, 4), 16) - 1;
                    EndsI = Convert.ToInt32(ColI.Substring(4, 4), 16);
                    //EndsI = System.Convert.ToInt16(ColI.Substring(4, 4));

                    if (YarnI > -1)
                    {
                        WM.SetValue(EndsI, YarnI, ColStart + i - 1);

                        WBi = Right("00" + (YarnI + 1).ToString(), 2);
                        WBj = Right("000" + (ColStart + i).ToString(), 3);
                        CellName = PatternPre + WBi + WBj;
                        exAgent.SetCellValue(CellName, EndsI, true);
                    }

                    R1i = System.Convert.ToInt16(ColI.Substring(8, 4));
                    WR.SetValue(R1i, 0, ColStart + i - 1);

                    WBi = "01";
                    WBj = Right("000" + (ColStart + i).ToString(), 3);
                    CellName = RepeatePre + WBi + WBj;
                    if (R1i == 0)
                        CellValue = "";
                    else
                        CellValue = R1i.ToString();

                    exAgent.SetCellValue(CellName, CellValue, true);

                    R2i = System.Convert.ToInt16(ColI.Substring(12, 4));
                    WR.SetValue(R2i, 1, ColStart + i - 1);
                    WBi = "02";
                    WBj = Right("000" + (ColStart + i).ToString(), 3);
                    CellName = RepeatePre + WBi + WBj;
                    if (R2i == 0)
                        CellValue = "";
                    else
                        CellValue = R2i.ToString();

                    exAgent.SetCellValue(CellName, CellValue, true);

                    R3i = System.Convert.ToInt16(ColI.Substring(16, 4));
                    WR.SetValue(R3i, 2, ColStart + i - 1);
                    WBi = "03";
                    WBj = Right("000" + (ColStart + i).ToString(), 3);
                    CellName = RepeatePre + WBi + WBj;
                    if (R3i == 0)
                        CellValue = "";
                    else
                        CellValue = R3i.ToString();
                    exAgent.SetCellValue(CellName, CellValue, true);
                    R4i = System.Convert.ToInt16(ColI.Substring(20, 4));
                    WR.SetValue(R4i, 3, ColStart + i - 1);
                    WBi = "04";
                    WBj = Right("000" + (ColStart + i).ToString(), 3);
                    CellName = RepeatePre + WBi + WBj;
                    if (R4i == 0)
                        CellValue = "";
                    else
                        CellValue = R4i.ToString();
                    exAgent.SetCellValue(CellName, CellValue, true);
                }
                WRWRQueue.Enqueue(WR);
                WRWRQueue.Enqueue(WM);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return WRWRQueue;
        }
        private void FillInWarpPattern(string PatternField, string PatternPre, string RepeatePre, string BeamPattern, string beamPos)
        {

            string CellName = "";
            string TableName = "";
            string FieldName = "";
            string sRow = "";
            string value = "";
            int wbi = 1;
            int wai = 1;
            Dictionary<string, string> WarpMatrix = PatternMatrix(22, 40, true);
            Array WR = Array.CreateInstance(typeof(Int32), 4, 698);
            Array WM = Array.CreateInstance(typeof(Int32), 100, 698);
            string PatternS = "";
            string YarnEnds = "";
            Queue CellQueue = new Queue();
            Queue WRWRQueue = new Queue();
            string WBi;
            string WBj;
            int maxwbi = 0;

            try
            {

                DataTable PatternTableSingle = dsSource.Tables["WTSAER"].Clone();
                DataRow dwPatternTableSingle;
                var queryWeavingBeamBase = from dwWTSAER in dsSource.Tables["WTSAER"].AsEnumerable()
                                           where dwWTSAER.Field<string>("WTBSRFNR") == BeamPattern
                                           select dwWTSAER;

                foreach (DataRow dwWTSAER in queryWeavingBeamBase)
                {
                    YarnEnds = dwWTSAER["WTBSRAPP"].ToString();
                    dwPatternTableSingle = PatternTableSingle.Rows.Add();
                    dwPatternTableSingle.ItemArray = dwWTSAER.ItemArray;
                    PatternTableSingle.AcceptChanges();
                    CellQueue.Clear();
                    CellQueue.Enqueue("WarpBeamCADKEY" + beamPos);
                    CellQueue.Enqueue("WTBCADKEY1");
                    FillInCellbyQueue(CellQueue, dwWTSAER);

                    CellName = "WARPPATCOM" + beamPos;
                    value = dwWTSAER["COMMENTCN"].ToString().Trim();
                    /*FillInCellbyQueue(CellQueue, dwWTSAER);*/
                    exAgent.SetCellValue(CellName, value, true);
                    #region
                    /*
                    foreach (DataColumn dcTemp in dwWTSAER.Table.Columns)
                    {
                        FieldName = dcTemp.ColumnName;
                        if (FieldName.Contains("WTBTAB"))
                        {
                            if (maxwbi >= 29)
                            {
                                WRWRQueue.Enqueue(WR);
                                WRWRQueue.Enqueue(WM);
                                WRWRQueue = HandleWTBTAB(WRWRQueue, FieldName, dwWTSAER, PatternPre, RepeatePre);
                                WR = (Array)WRWRQueue.Dequeue();
                                WM = (Array)WRWRQueue.Dequeue();
                            }
                        }
                        CellName = dcTemp.ColumnName;
                        value = GetGB2312Value(dwWTSAER[FieldName].ToString().Trim());
                        if (FieldName.Contains("WTBFNR"))
                        {
                            wai = Convert.ToInt16(value) - 1;
                        }
                        if (FieldName.Contains("WTBABSCHUSS0"))
                        {
                            sRow = Right(FieldName, 2);
                            wbi = Convert.ToInt16(sRow) - 1;

                            if (wai > -1 && wbi > -1)
                            {
                                if (wbi > maxwbi)
                                {
                                    maxwbi = wbi;
                                }
                                WBi = Right("00" + (wai + 1).ToString(), 2);
                                WBj = Right("000" + (wbi + 1).ToString(), 3);
                                CellName = PatternPre + WBi + WBj;
                                WM.SetValue(System.Convert.ToInt16(value), wai, wbi);
                            }
                        }
                        if (FieldName.Contains("WTBWDHFAKT"))
                        {
                            sRow = Right(FieldName, 4);
                            int R = System.Convert.ToInt16(Left(sRow, 1)) - 1;
                            int C = System.Convert.ToInt16(Right(sRow, 2)) - 1;

                            WR.SetValue(System.Convert.ToInt16(value), R, C);
                            WBi = Right("00" + (R + 1).ToString(), 2);
                            WBj = Right("000" + (C + 1).ToString(), 3);
                            sRow = WBi + WBj;
                            CellName = RepeatePre + sRow;
                            if (value == "0")
                                value = "";
                        }
                        exAgent.SetCellValue(CellName, value, true);
                    }
                        */
                    #endregion


                    #region
                    string WTBFNR, WTBANZFAE, WTBWDHFAKTi;
                    for (int YNi = 1; YNi <= 100; YNi++)
                    {
                        //    WTBFNR59	WTBANZFAE59	WTBWDHFAKT591	WTBWDHFAKT592	WTBWDHFAKT593	WTBWDHFAKT594
                        //WTBFNR: which yarn
                        //WTBANZFAE how many ends
                        //WTBWDHFAKT591 repeats
                        /*
                         *                         WTBFNR008
WTBABSCHUSS008
WTBWDHFAKT1008
WTBWDHFAKT2008
WTBWDHFAKT3008
WTBWDHFAKT4008
                         */

                        int Ends = 0;
                        WTBFNR = "WTBFNR" + YNi.ToString();
                        WTBANZFAE = "WTBANZFAE" + YNi.ToString();
                        wai = System.Convert.ToInt16(dwWTSAER[WTBFNR].ToString()) - 1;
                        Ends = System.Convert.ToInt16(dwWTSAER[WTBANZFAE].ToString());
                        wbi = YNi - 1;
                        WBi = Right("00" + (wai + 1).ToString(), 2);
                        WBj = Right("000" + (wbi + 1).ToString(), 3);
                        CellName = PatternPre + WBi + WBj;
                        if (wai > -1 && wbi > -1)
                        {
                            WM.SetValue(Ends, wai, wbi);
                        }
                        exAgent.SetCellValue(CellName, Ends, true);
                        sRow = Right(FieldName, 4);
                        int C = YNi - 1;
                        int RPi = 0;
                        for (int R = 1; R < 5; R++)
                        {
                            WTBWDHFAKTi = "WTBWDHFAKT" + YNi.ToString() + R.ToString();
                            RPi = System.Convert.ToInt16(dwWTSAER[WTBWDHFAKTi].ToString());
                            WR.SetValue(RPi, R - 1, C);
                            WBi = Right("00" + (R).ToString(), 2);
                            WBj = Right("000" + (C + 1).ToString(), 3);
                            sRow = WBi + WBj;
                            CellName = RepeatePre + sRow;
                            value = RPi.ToString();
                            if (RPi == 0)
                            {
                                value = "";
                            }
                            exAgent.SetCellValue(CellName, value, true);
                        }
                    }
                    #endregion
                }

                TableName = "WTSAER";
                PatternS = BuildPatternString(WR, WM, TableName);
                PatternS = PatternS.Replace("+", "");
                CellName = PatternField;
                exAgent.SetCellValue(CellName, PatternS, true);
                CellName = "WTBSRAPP" + Right(PatternField, 2).ToString();
                exAgent.SetCellValue(CellName, YarnEnds, true);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInWarpYarnWS31(string TgaType, DataTable dtBastga)
        {
            string YarnArticle;
            string YarnVar;
            string Article;
            string Var;
            string YarnColor;
            string Yarn;
            string ProcessedYarn;
            DataTable dtBastke = null;
            string KART = "";
            string KVAR = "";
            string KFARBE = "";
            string KANZ = "";

            dtBastke = dsSource.Tables["WASTKE"];
            KART = "WAKKART";
            KVAR = "WAKKVAR";
            KFARBE = "WAKKFARBE";
            KANZ = "WAKEAFAKTR";

            #region WarpYarn
            var query = from dwBastga in dtBastga.AsEnumerable()
                        let BeamArticle = dwBastga.Field<string>("BAGART") + dwBastga.Field<string>("BAGVAR") + dwBastga.Field<string>("BAGFARBE")
                        join dWBastke in dtBastke.AsEnumerable()
                       on BeamArticle equals dWBastke.Field<string>(KART) + dWBastke.Field<string>(KVAR) + dWBastke.Field<string>(KFARBE)
                        group new { dwBastga, dWBastke } by new
                        {
                            YarnPos = dwBastga["BAGPOS"],
                            YarnArticle = dwBastga["BAGGART"],
                            YarnVar = dwBastga["BAGGVAR"],
                            YarnColor = dwBastga["BAGGFARBE"]
                        }
                            into grp
                            select new
                            {
                                Yarn = grp.Key,
                                TtlBAGKGNET = grp.Sum(g => g.dwBastga.Field<decimal>("BAGKGNET") * g.dWBastke.Field<decimal>(KANZ)),
                                TtlBAGKGBRUT = grp.Sum(g => g.dwBastga.Field<decimal>("BAGKGBRUT") * g.dWBastke.Field<decimal>(KANZ)),
                                TtlEnds = grp.Sum(g => g.dwBastga.Field<decimal>("BAGFDZ")),
                                MaxWastage = grp.Max(g => g.dwBastga.Field<decimal>("BAGABF")),
                            };
            #endregion
            /*select   BAGGART, BAGGVAR, BAGGFARBE, SUM(BAGFDZ*BAKANZ) from INTEX.BASTGA b,bastke A
where B.BAGART=BAKKART AND B.BAGVAR=BAKKVAR AND BAGfarbe=BAKKFARBE
AND BAKART LIKE 'PT14A750D-1052%'AND BAKVAR LIKE '1%'
group by B.BAGGART,b.BAGGVAR,b.BAGGFARBE*/
            int YarnNum = 0;
            string YarnPosition = "";
            Queue CellQ = new Queue();
            string YarnBase = "";
            string Yarndes = "";
            string YarnVardes = "";
            foreach (var varYarn in query)
            {
                YarnNum++;
                //YarnPosition = Right("00" + YarnNum.ToString(), 2);
                YarnPosition = varYarn.Yarn.YarnPos.ToString();
                Article = varYarn.Yarn.YarnArticle.ToString();
                Var = varYarn.Yarn.YarnVar.ToString();
                Yarn = varYarn.Yarn.YarnArticle.ToString().Trim() + "/" + varYarn.Yarn.YarnVar.ToString().Trim() + "/" + varYarn.Yarn.YarnColor.ToString().Trim();
                YarnBase = varYarn.Yarn.YarnArticle.ToString().Trim() + "/" + varYarn.Yarn.YarnVar.ToString().Trim();
                Yarn = GetGB2312Value(Yarn);
                DataRow[] dwtemp = dsSource.Tables["GSDAT"].Select("GSART='" + Article + "' AND GSVAR='" + Var + "'");
                if (dwtemp.Count() > 0)
                {
                    DataRow dwt = dwtemp[0];
                    YarnVardes = GetGB2312Value(dwt["GSGARNBEZ"].ToString().TrimEnd());
                };
                CellQ.Enqueue("WarpYarn" + YarnPosition);
                CellQ.Enqueue(Yarn.ToString());
                CellQ.Enqueue("WWarpYarn" + YarnPosition);
                CellQ.Enqueue(Yarn.ToString() + Environment.NewLine + YarnVardes);
                CellQ.Enqueue("WarpYarnDes" + YarnPosition);
                CellQ.Enqueue(GetYarnDes(dsSource.Tables["GSCOLOR"], Yarn));
                string TtlBAGKGNET = varYarn.TtlBAGKGNET.ToString();
                string TtlBAGKGBRUT = varYarn.TtlBAGKGBRUT.ToString();
                string TtlEnds = varYarn.TtlEnds.ToString();
                string MaxWastage = varYarn.MaxWastage.ToString();
                Jdense += ComputeDense(Warpdensity, WarpEnds, System.Convert.ToDouble(TtlEnds), YarnBase);
                CellQ.Enqueue("WarpYarnNet" + YarnPosition);
                CellQ.Enqueue(TtlBAGKGNET);
                CellQ.Enqueue("WarpYarnGross" + YarnPosition);
                CellQ.Enqueue(TtlBAGKGBRUT);
                CellQ.Enqueue("WarpYarnEnds" + YarnPosition);
                CellQ.Enqueue(TtlEnds);
                CellQ.Enqueue("MaxWastage" + YarnPosition);
                CellQ.Enqueue(MaxWastage);
            }
            FillInCellbyQueue(CellQ);
            string CellName = "";
            string Cellvalue = "";
            CellName = "Jdense";
            Cellvalue = Jdense.ToString();
            exAgent.SetCellValue(CellName, Cellvalue, true);

        }
        private void FillInWarpYarnBS08(string TgaType, DataTable dtBastga)
        {
            string YarnArticle;
            string YarnVar;
            string YarnColor;
            string Yarn;
            string ProcessedYarn;
            DataTable dtBastke = null;
            string KART = "";
            string KVAR = "";
            string KFARBE = "";
            string KANZ = "";


            dtBastke = dsSource.Tables["BASTKE"];
            KART = "BAKKART";
            KVAR = "BAKKVAR";
            KFARBE = "BAKKFARBE";
            KANZ = "BAKANZ";
            #region WarpYarn
            var query = from dwBastga in dtBastga.AsEnumerable()
                        let BeamArticle = dwBastga.Field<string>("BAGART") + dwBastga.Field<string>("BAGVAR") + dwBastga.Field<string>("BAGFARBE")
                        join dWBastke in dtBastke.AsEnumerable()
                       on BeamArticle equals dWBastke.Field<string>(KART) + dWBastke.Field<string>(KVAR) + dWBastke.Field<string>(KFARBE)
                        group new { dwBastga, dWBastke } by new
                        {
                            YarnPos = dwBastga["BAGPOS"],
                            YarnArticle = dwBastga["BAGGART"],
                            YarnVar = dwBastga["BAGGVAR"],
                            YarnColor = dwBastga["BAGGFARBE"]
                        }
                            into grp
                            select new
                            {
                                Yarn = grp.Key,
                                TtlBAGKGNET = grp.Sum(g => g.dwBastga.Field<decimal>("BAGKGNET") * g.dWBastke.Field<decimal>(KANZ)),
                                TtlBAGKGBRUT = grp.Sum(g => g.dwBastga.Field<decimal>("BAGKGBRUT") * g.dWBastke.Field<decimal>(KANZ)),
                                TtlEnds = grp.Sum(g => g.dwBastga.Field<decimal>("BAGFDZ") * g.dWBastke.Field<decimal>(KANZ)),
                                MaxWastage = grp.Max(g => g.dwBastga.Field<decimal>("BAGABF")),
                            };
            #endregion
            /*select   BAGGART, BAGGVAR, BAGGFARBE, SUM(BAGFDZ*BAKANZ) from INTEX.BASTGA b,bastke A
where B.BAGART=BAKKART AND B.BAGVAR=BAKKVAR AND BAGfarbe=BAKKFARBE
AND BAKART LIKE 'PT14A750D-1052%'AND BAKVAR LIKE '1%'
group by B.BAGGART,b.BAGGVAR,b.BAGGFARBE*/
            int YarnNum = 0;
            string YarnPosition = "";
            Queue CellQ = new Queue();
            string YarnBase = "";
            string Yarndes = "";
            foreach (var varYarn in query)
            {
                YarnNum++;
                YarnPosition = varYarn.Yarn.YarnPos.ToString();
                Yarn = varYarn.Yarn.YarnArticle.ToString().Trim() + "/" + varYarn.Yarn.YarnVar.ToString().Trim() + "/" + varYarn.Yarn.YarnColor.ToString().Trim();
                YarnBase = varYarn.Yarn.YarnArticle.ToString().Trim() + "/" + varYarn.Yarn.YarnVar.ToString().Trim();
                Yarn = GetGB2312Value(Yarn);
                CellQ.Enqueue("WarpYarn" + YarnPosition);
                CellQ.Enqueue(Yarn.ToString());
                CellQ.Enqueue("WarpYarnDes" + YarnPosition);
                CellQ.Enqueue(GetYarnDes(dsSource.Tables["GSCOLOR"], Yarn));
                string TtlBAGKGNET = varYarn.TtlBAGKGNET.ToString();
                string TtlBAGKGBRUT = varYarn.TtlBAGKGBRUT.ToString();
                string TtlEnds = varYarn.TtlEnds.ToString();
                string MaxWastage = varYarn.MaxWastage.ToString();
                Jdense += ComputeDense(Warpdensity, WarpEnds, System.Convert.ToDouble(TtlEnds), YarnBase);
                CellQ.Enqueue("WarpYarnNet" + YarnPosition);
                CellQ.Enqueue(TtlBAGKGNET);
                CellQ.Enqueue("WarpYarnGross" + YarnPosition);
                CellQ.Enqueue(TtlBAGKGBRUT);
                CellQ.Enqueue("WarpYarnEnds" + YarnPosition);
                CellQ.Enqueue(TtlEnds);
                CellQ.Enqueue("MaxWastage" + YarnPosition);
                CellQ.Enqueue(MaxWastage);
            }
            FillInCellbyQueue(CellQ);
            string CellName = "";
            string Cellvalue = "";
            CellName = "Jdense";
            Cellvalue = Jdense.ToString();
            exAgent.SetCellValue(CellName, Cellvalue, true);

        }
        private void FillInWarpYarn(string TgaType, DataTable dtBastga)
        {
            string YarnArticle;
            string YarnVar;
            string YarnColor;
            string Yarn;
            string ProcessedYarn;
            DataTable dtBastke = null;
            string KART = "";
            string KVAR = "";
            string KFARBE = "";
            string KANZ = "";

            switch (TgaType)
            {
                case "BS08":
                    FillInWarpYarnBS08(TgaType, dtBastga);
                    break;
                case "WS31":
                    FillInWarpYarnWS31(TgaType, dtBastga);
                    break;
                default:
                    break;
            }

        }
        private double ComputeDense(double TTLdensity, double TTLEnds, double YarnEnds, string Yarn)
        {
            double YarnDense = 0;
            double YarnCount = 0;

            var query = from dwGSDAT in dsSource.Tables["GSDAT"].AsEnumerable()
                        where dwGSDAT["GSART"].ToString().Trim() + "/" + dwGSDAT["GSVAR"].ToString().Trim() == Yarn
                        select dwGSDAT;

            foreach (DataRow dwYarn in query)
            {
                YarnCount = System.Convert.ToDouble(dwYarn["GSRECHNR"].ToString());
                YarnCount = System.Math.Sqrt(590.5 / YarnCount);
                YarnDense = 0.037 * TTLdensity * YarnEnds / TTLEnds * YarnCount;
            }
            return YarnDense;
        }
        private void FillInWeftYarn(string TgaType, DataTable dtWastga)
        {
            string YarnArticle;
            string YarnVar;
            string Article;
            string Var;
            string YarnColor;
            string YarnPos;
            string Yarn;
            string ProcessedYarn;
            string YarnBase = "";
            #region WeftYarn
            var query = from dwWastga in dtWastga.AsEnumerable()
                        group dwWastga by new
                        {
                            YarnArticle = dwWastga["WAGGART"],
                            YarnVar = dwWastga["WAGGVAR"],
                            YarnColor = dwWastga["WAGGFARBE"],
                            YarnPos = dwWastga["WAGFOLGEPOS"]
                        }
                            into g
                            select new
                            {
                                Yarn = g.Key,
                                TtlBAGKGNET = g.Sum(dwWastga => dwWastga.Field<decimal>("WAGKGNET")),
                                TtlBAGKGBRUT = g.Sum(dwWastga => dwWastga.Field<decimal>("WAGKGBRUT")),
                                TtlEnds = g.Sum(dwWastga => dwWastga.Field<decimal>("WAGFDZ")),
                                MaxWastage = g.Max(dwWastga => dwWastga.Field<decimal>("WAGABF")),
                            };
            int YarnNum = 0;
            string YarnPosition = "";
            string YarnVardes = "";
            Queue CellQ = new Queue();
            foreach (var varYarn in query)
            {
                YarnNum++;
                YarnPosition = Right("00" + YarnNum.ToString(), 2);
                Article = varYarn.Yarn.YarnArticle.ToString();
                Var = varYarn.Yarn.YarnVar.ToString();
                Yarn = varYarn.Yarn.YarnArticle.ToString().Trim() + "/" + varYarn.Yarn.YarnVar.ToString().Trim() + "/" + varYarn.Yarn.YarnColor.ToString().Trim();
                Yarn = GetGB2312Value(Yarn);
                YarnBase = varYarn.Yarn.YarnArticle.ToString().Trim() + "/" + varYarn.Yarn.YarnVar.ToString().Trim();
                DataRow[] dwtemp = dsSource.Tables["GSDAT"].Select("GSART='" + Article + "' AND GSVAR='" + Var + "'");
                if (dwtemp.Count() > 0)
                {
                    DataRow dwt = dwtemp[0];
                    YarnVardes = GetGB2312Value(dwt["GSGARNBEZ"].ToString().TrimEnd());
                };
                CellQ.Enqueue("WWAGGART" + YarnPosition);
                CellQ.Enqueue(Yarn.ToString() + Environment.NewLine + YarnVardes);
                CellQ.Enqueue("WAGGART" + YarnPosition);
                CellQ.Enqueue(Yarn.ToString());
                CellQ.Enqueue("WeftYarnDes" + YarnPosition);
                CellQ.Enqueue(GetYarnDes(dsSource.Tables["GSCOLOR"], Yarn));
                string TtlBAGKGNET = varYarn.TtlBAGKGNET.ToString();
                string TtlBAGKGBRUT = varYarn.TtlBAGKGBRUT.ToString();
                string TtlEnds = varYarn.TtlEnds.ToString();
                string MaxWastage = varYarn.MaxWastage.ToString();
                Wdense += ComputeDense(Weftdensity, WeftEnds, System.Convert.ToDouble(TtlEnds), YarnBase);
                CellQ.Enqueue("WAGKGBNET" + YarnPosition);
                CellQ.Enqueue(TtlBAGKGNET);
                CellQ.Enqueue("WAGKGBRUT" + YarnPosition);
                CellQ.Enqueue(TtlBAGKGBRUT);
                CellQ.Enqueue("WAGFDZ" + YarnPosition);
                CellQ.Enqueue(TtlEnds);
                CellQ.Enqueue("WAGABF" + YarnPosition);
                CellQ.Enqueue(MaxWastage);
            }
            FillInCellbyQueue(CellQ);
            string CellName = "";
            string Cellvalue = "";
            CellName = "Wdense";
            Cellvalue = Wdense.ToString();
            exAgent.SetCellValue(CellName, Cellvalue, true);
            #endregion
        }
        public override void WriteExcelReport(string fileType)
        {
            GetGreigeMasterReport(fileType);
            base.WriteExcelReport(fileType);
        }
        public void FillInWEAVEPATTERN(DataSet setData)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setWEAVE = new DataSet();
            INTEXDataSet setPATTERN = new INTEXDataSet();
            if (strCompany != "HWD")
                return;
            try
            {
                DataRow dwWASTAMM = setData.Tables["WASTAMM"].Rows[0];
                DataRow dwWASTPASS = setData.Tables["WASTPASS"].Rows[0];
                string PGAART = dwWASTAMM["WABIND"].ToString();
                string DFAART = dwWASTPASS["WSPPASS"].ToString();
                BaseFunction m_AD = new BaseFunction();
                setWEAVE=m_AD.LoadSetWEAVE();
                setResult = m_AD.QueryWeave(setWEAVE, PGAART, DFAART);
                setPATTERN.Merge(setResult);
                int rStart=29;
                int cStart=3;
                string pColumn="";
                string value = "";
                var query = from DataRow dwTemp in setPATTERN.PEGPLAN.AsEnumerable()
                            orderby dwTemp["FN"]
                            descending
                            select dwTemp;
                foreach (DataRow dwTemp in query)
                { 
                    rStart++;
                    cStart=3;
                    for(int pi=1;pi<=16;pi++)
                    {
                        cStart++;
                        pColumn="PG"+m_AD.Right("00"+pi.ToString(),2);
                        if (dwTemp.Table.Columns.Contains(pColumn))
                        {
                            exAgent.SetCellValue(rStart, cStart, dwTemp[pColumn].ToString());
                        }
                    }  
                }
                cStart = 23;
                rStart = 29;
                query = from DataRow dwTemp in setPATTERN.DRAFTING.AsEnumerable()
                        orderby dwTemp["FN"]
                        descending
                        select dwTemp;
                foreach (DataRow dwTemp in query)
                { 
                     exAgent.SetCellValue(29,24,dwTemp["DFADES"].ToString());
                    cStart++;
                    for (int pi = 1; pi <= 16; pi++)
                    { 
                        pColumn = "D" + m_AD.Right("00" + pi.ToString(), 2);
                        if (dwTemp.Table.Columns.Contains(pColumn))
                        { 
                            exAgent.SetCellValue(rStart + pi, cStart, dwTemp[pColumn].ToString());
                        }
                    }  
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            } 
        }
        public void GetGreigeMasterReport(string fileType)
        {

            var query = from dwWASTAMM in dsSource.Tables["WASTAMM"].AsEnumerable()
                        select dwWASTAMM;

            string strSheetName;
            string CellName = "";
            string TableName = "";
            string FieldName = "";
            bool MultiRow = false;
            bool PatternTable = false;
            int Rown = 0;
            string sRow = "";
            Queue CellQueue = new Queue();
            string value = "";
            int wbi = 1;
            int wai = 1;
            Dictionary<string, string> WarpMatrix = PatternMatrix(9, 40, true);
            Array WR = Array.CreateInstance(typeof(Int32), 4, 30);
            Array WM = Array.CreateInstance(typeof(Int32), 9, 30);
            string PatternS = "";
            string SelvageWeave = "";
            string SelvageCount = "";

            string filename = "";
            string sheetName = "";
            string Standard = "Standard";

            string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\" + strExcelFile);
                
            filePath = strExcelFile;
            exAgent.Open(filePath);
            exAgent.Company = strCompany;
            exAgent.strExcelFile = strExcelFile;
            GreigeMasterData m_GM = new GreigeMasterData();
            if (true)
            {
                filename = Path.Combine(PathHelper.ApplicationPath, @"Report\" + ArticleNum + ".xls");
                sheetName =m_GM.Convert8859P1ToGB2312(ArticleNum + "_" + ArticleVar + "_" + ArticleColor + "_" + ArticleFin);
                exAgent.CreateNewSheet(filename, Standard, sheetName);
                CellName = "PrintDate";
                value = System.DateTime.Today.ToShortDateString();
                exAgent.SetCellValue(CellName, value, true);
                //exAgent.HiddenWorkSheet(sheetName);
                Warpdensity = System.Convert.ToDouble(dsSource.Tables["WASTAMM"].Rows[0]["WARFDDIK"].ToString());
                Warpdensity = Warpdensity / 2.54 * 10;
                Weftdensity = System.Convert.ToDouble(dsSource.Tables["WASTAMM"].Rows[0]["WARFDDIS"].ToString());
                Weftdensity = Weftdensity / 2.54 * 10;
                #region BySequence
                FillInFinish(dsSource.Tables["WAFINISH"]);
                FillInWeaveColor(dsSource.Tables["WAFARBEN"]);
                FillInComposition(dsSource.Tables["WASTAMM"]);
                FillInWeftPattern(dsSource.Tables["WTSCHUSS"]);
                if (dsSource.Tables["WASTKE"].Rows.Count > 0)
                {
                    FillInWarpBeam("WS31", dsSource.Tables["WASTKE"].Rows[0]);
                    FillInSelvage(dsSource.Tables["WASTKE"].Rows[0]);
                    TotalEnds = WarpBeamEnds11 + TotalSelvage;
                    CellName = "TotalEnds";
                    exAgent.SetCellValue(CellName, TotalEnds, true);
                }
                else
                {
                    Tracer.Verbose("", "WS31 设定错误");
                    Tracer.Speak("WS31 设定错误");
                    //MessageBox.Show("WS31 设定错误");
                }
                FillInWarpYarn("BS08", dsSource.Tables["BASTGA"]);
                FillInWarpYarn("WS31", dsSource.Tables["BASTGA"]);
                FillInWeftYarn("WS33", dsSource.Tables["WASTGA"]);
                TotalDense = Jdense + Wdense - (Jdense * Wdense / 100);
                CellName = "TotalDense";
                exAgent.SetCellValue(CellName, TotalDense, true);
                FillInWEAVEPATTERN(dsSource);
                #endregion
                #region DataTable
                foreach (DataTable dtArticle in dsSource.Tables)
                {
                    TableName = dtArticle.TableName;


                    if (TableName == "ARTICLE" || TableName == "ARTICLECOLOR" || TableName == "ARTICLEVAR"
                        || TableName == "ARTICLEFINISH" || TableName == "Pattern")
                    {
                        continue;
                    }
                    MultiRow = false;
                    PatternTable = false;
                    Rown = 0;
                    if (MultiRowTable.Contains(TableName + ";"))
                    {
                        MultiRow = true;
                    }
                    if (PatternTables.Contains(TableName + ";"))
                    {
                        PatternTable = true;
                    }

                    if (TableName == "WTSCHUSS"
                        || TableName == "WAFINISH"
                        || TableName == "BASTGA"
                        || TableName == "WASTGA"
                        || TableName == "WASTKE"
                        || TableName == "WAFARBEN"
                        )
                    {
                        continue;
                    }
                    #region DataRow
                    foreach (DataRow dwArticle in dtArticle.Rows)
                    {
                        Rown++;

                        if (TableName == "WASTPASS")
                        {
                            FillInDrafting(dwArticle);
                            continue;
                        }
                        if (TableName == "WASTAMM")
                        {
                            FillInWeave(dwArticle);
                        }
                        #region DataColumn
                        foreach (DataColumn dc in dwArticle.Table.Columns)
                        {
                            CellName = dc.ColumnName;
                            if (MultiRow)
                            {
                                sRow = "00" + Rown.ToString();
                                sRow = Right(sRow, 2);
                                CellName = CellName + sRow;
                            }
                            //exAgent.SetCellValue(CellName, value, true);
                            CellQueue.Enqueue(CellName);
                            CellQueue.Enqueue(dc.ColumnName);
                            FillInCellbyQueue(CellQueue, dwArticle);
                        }
                    }
                        #endregion
                    #endregion
                #endregion


                }
            }
            if (exAgent != null)
            {
                if (strCompany == "HLT")
                {
                    HideNullRow();
                }
                else
                {
                    HideNullRow(strCompany);
                }
                exAgent.Save();
                //MessageBox.Show("你是否打开Exclel?",,MessageBoxButtons.YesNo,MessageBoxDefaultButton.Button2);

                exAgent.Close();
            }
        }
        private void HideNullRow()
        {
            try
            {
                int HidedRows = 0;
                int rowstart = 1;
                int rowend = 1;
                int col1, col2, col3;

                var val1 = exAgent.GetCellValue(1, 1);
                var val2 = exAgent.GetCellValue(1, 1);
                var val3 = exAgent.GetCellValue(1, 1);
                //Case 1
                rowstart = 30; rowend = 51; col1 = 3;
                for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                {
                    val1 = exAgent.GetRangeValue(Rowi, Rowi, 9, 38);
                    if ((string)val1 == "0")
                    {
                        HidedRows++;
                        exAgent.HideRows(Rowi, col1, Rowi, col1);
                    }
                }
                rowstart = 53; rowend = 56; col1 = 2; col2 = 3;
                for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                {
                    val1 = exAgent.GetRangeValue(Rowi, Rowi, 9, 38);
                    if ((string)val1 == "0")

                        //.get_Value(Type.Missing);
                        if ((string)val1 == "0")
                        {
                            HidedRows++;
                            exAgent.HideRows(Rowi, col1, Rowi, col1);
                        }
                }
                rowstart = 341; rowend = 351; col1 = 17; col2 = 23;
                for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                {
                    val1 = exAgent.GetRangeValue(Rowi, Rowi, 3, 4);
                    val2 = exAgent.GetRangeValue(Rowi, Rowi, 21, 22);
                    if ((string)val1 == "0" && (string)val2 == "0")
                    {
                        HidedRows++;
                        exAgent.HideRows(Rowi, col1, Rowi, col2);
                    }
                }
                /*
                             rowstart = 381 + HidedRows - 21; rowend = 387 + HidedRows - 21; col1 = 3; col2 = 22;
                             for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                             {
                                 val1 = exAgent.GetCellValue(Rowi, col1);
                                 val2 = exAgent.GetCellValue(Rowi, col2);
                                 if (val1 == null && val2 == null)
                                 {
                                     HidedRows++;
                                     exAgent.HideRows(Rowi, col1, Rowi, col2);
                                 }
                             }
                             */
                int InsertedRows = HidedRows - 22;
                if (HidedRows > 0)
                {
                    rowstart = 359;
                    exAgent.InsertRows(rowstart, HidedRows - 22);
                }
                rowstart = 433 + InsertedRows; rowend = 1800 + InsertedRows;
                for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                {
                    val1 = exAgent.GetRangeValue(Rowi, Rowi, 8, 34);
                    if ((string)val1 == "0")
                    {
                        if (Rowi == rowstart)
                            Rowi = Rowi - 1;
                        exAgent.HideRows(Rowi, col1, rowend, col1);
                        Rowi = rowend;
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void HideNullRow(string Company = "HWD")
        {
            try
            {
                int HidedRows = 0;
                int rowstart = 1;
                int rowend = 1;
                int col1, col2, col3;

                var val1 = exAgent.GetCellValue(1, 1);
                var val2 = exAgent.GetCellValue(1, 1);
                var val3 = exAgent.GetCellValue(1, 1);
                //Case 1
                /*
                rowstart = 34; rowend = 55; col1 = 3;
                for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                {
                    val1 = exAgent.GetRangeValue(Rowi, Rowi, 9, 38);
                    if ((string)val1 == "0")
                    {
                        HidedRows++;
                        exAgent.HideRows(Rowi, col1, Rowi, col1);
                    }
                }
                rowstart = 57; rowend = 60; col1 = 2; col2 = 3;
                */
                rowstart = 34; rowend = 352; col1 = 2; col2 = 3;

                for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                {
                    val1 = exAgent.GetRangeValue(Rowi, Rowi, 9, 38);
                    if ((string)val1 == "0")

                        //.get_Value(Type.Missing);
                        if ((string)val1 == "0")
                        {
                            HidedRows++;
                            exAgent.HideRows(Rowi, col1, Rowi, col1);
                        }
                }
                rowstart = 355; rowend = 365; col1 = 17; col2 = 23;
                for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                {
                    val1 = exAgent.GetRangeValue(Rowi, Rowi, 3, 4);
                    val2 = exAgent.GetRangeValue(Rowi, Rowi, 34, 35);
                    if ((string)val1 == "0" && (string)val2 == "0")
                    {
                        HidedRows++;
                        exAgent.HideRows(Rowi, col1, Rowi, col2);
                    }
                }
                int InsertedRows = 0;
                /*
                InsertedRows = HidedRows - 18;
                if (HidedRows > 0)
                {
                    rowstart = 380;
                    exAgent.InsertRows(rowstart, HidedRows - 18);
                }
                */
                rowstart = 400 + InsertedRows; rowend = 1800 + InsertedRows;
                for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                {
                    val1 = exAgent.GetRangeValue(Rowi, Rowi, 8, 34);
                    if ((string)val1 == "0")
                    {
                        if (Rowi == rowstart)
                            Rowi = Rowi - 1;
                        exAgent.HideRows(Rowi, col1, rowend, col1);
                        Rowi = rowend;
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

    }

}
